# -*- coding: utf-8 -*-
"""
Created on Sat May 13 09:17:15 2017

@author: Tubin Huang
"""

CONFIG_FILE = "./sql.conf"

import ConfigParser

import mysql.connector

from sshtunnel import SSHTunnelForwarder

class SQLService(object):
    def __init__(self, configFile = CONFIG_FILE ):
        assert isinstance( configFile, str )
        _configFile = configFile
        cp = ConfigParser.ConfigParser()
        cp.read( _configFile )
      
        self.ssh_host = cp.get("ssh", "host" )
        self.ssh_port = cp.get("ssh", "port" )
        self.ssh_user = cp.get("ssh", "user" )
        self.ssh_password = cp.get("ssh", "password" )
        
        self.database_host = cp.get("database", "host" )
        self.database_port = cp.get("database", "port" )
        self.database_user = cp.get("database", "user" )
        self.database_name = cp.get( "database", "name")
        self.database_password = cp.get("database", 'password')

        self.service = SSHTunnelForwarder(\
        ssh_address = ( self.ssh_host, int(self.ssh_port )),\
        ssh_username = self.ssh_user,\
        ssh_password = self.ssh_password,\
        remote_bind_address = ( self.database_host, int(self.database_port )))
        
        
        self.service.start()
    
    def connect( self ):
        print self.service.local_bind_port
        conn = mysql.connector.connect(host = self.database_host, port = self.service.local_bind_port, user = self.database_user, password = self.database_password, database =  self.database_name, use_unicode=True)
        cur = conn.cursor()
        cur.execute("show databases")
        shows = cur.fetchall()
        print shows
        cur.close()
        conn.close()        
        return conn
        
        
        
    def createService():
        return SQLService()

if __name__ == "__main__":
    s = SQLService()
    s.connect()
    s.service.stop()
    
    